﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace Water.Web.Controls.Office.Excel.Util
{
    public class QueryTable
    {
        private const string VAR_NAME_PREFEX = "qt";
        private const string VAR_COUNT_PREFEX = "count";
        private Water.Web.Controls.Office.Excel.Sheet sheet;
        private int sheetNum;
        private string var_name;
        private string var_count;
        private int row;
        private int col;

        public QueryTable(int sheetNum, Water.Web.Controls.Office.Excel.Sheet sheet)
        {
            this.sheetNum = sheetNum;
            this.sheet = sheet;
            var_name = VAR_NAME_PREFEX + this.sheetNum;
            var_count = VAR_COUNT_PREFEX + this.sheetNum;
            this.row = sheet.Row;
            this.col = sheet.Col;

        }

        internal int SheetNum
        {
            get { return sheetNum; }
        }
        
        internal string VarName
        {
            get { return var_name; }
        }
        
        internal string VarCount
        {
            get { return var_count; }
            set { var_count = value; }
        }
        
        internal int Row
        {
            get { return row; }
            set { row = value; }
        }
        
        internal int Col
        {
            get { return col; }
            set { col = value; }
        }
        public string CreateQueryTable(string url, string dataTypes)
        {
            StringBuilder _queryTable = new StringBuilder();
            string startCell = Util.GetCellName(this.row, this.col);
            _queryTable.Append("\nversion = CDbl(Excel.Version)\n");
            _queryTable.Append("Set " + var_name + "= Excel.ActiveSheet.QueryTables.Add(\"TEXT;" + url + "\",Excel.ActiveSheet." + startCell + ")\n");
            _queryTable.Append("With " + var_name + "\n");
            _queryTable.Append("    .FieldNames = True\n");
            _queryTable.Append("    .RowNumbers = False\n");
            _queryTable.Append("    .FillAdjacentFormulas = False\n");
            _queryTable.Append("    .PreserveFormatting = True\n");
            _queryTable.Append("    .RefreshOnFileOpen = False\n");
            _queryTable.Append("    .RefreshStyle = 1\n");
            _queryTable.Append("    .SavePassword = False\n");
            _queryTable.Append("    .SaveData = True\n");
            _queryTable.Append("    .AdjustColumnWidth = True\n");
            _queryTable.Append("    .RefreshPeriod = 0\n");
            _queryTable.Append("    .TextFilePromptOnRefresh = False \n");
            _queryTable.Append("    If Version<10 then \n");
            _queryTable.Append("        .TextFilePlatform = 2 \n");
            _queryTable.Append("    else\n");
            _queryTable.Append("        .TextFilePlatform = 936 \n");
            _queryTable.Append("    end if \n");
            _queryTable.Append("    .TextFileStartRow = 1\n");
            _queryTable.Append("    .TextFileParseType = 1\n");
            _queryTable.Append("    .TextFileTextQualifier = 1\n");
            _queryTable.Append("    .TextFileConsecutiveDelimiter = False\n");
            _queryTable.Append("    .TextFileTabDelimiter = True\n");
            _queryTable.Append("    .TextFileSemicolonDelimiter = False\n");
            _queryTable.Append("    .TextFileCommaDelimiter = False\n");
            _queryTable.Append("    .TextFileSpaceDelimiter = False\n");
            _queryTable.Append("    .TextFileColumnDataTypes = Array(" + dataTypes + ")\n");
            _queryTable.Append("    .Refresh\n");
            _queryTable.Append("End With\n");
            _queryTable.Append(this.var_count + "=" + var_name + ".ResultRange.Rows.Count\n");

            return _queryTable.ToString();

        }
        
        public string CreateTabelCellStyle(string tableCellStyle)
        {
            string _ret = this.var_name + ".ResultRange.Style=\"" + tableCellStyle + "\"\n";
            return _ret;
        }
        
        public string CreateTabelHeadStyle(string tableHeadStyle)
        {
            string _ret = this.var_name + ".ResultRange.Rows(1).Style=\"" + tableHeadStyle + "\"\n";
            return _ret;
        }
        
        public string CreateTableFooterStyle(string tableFooterStyle)
        {
            string _ret = var_name + ".ResultRange.Rows(" + var_count + "+1).Style=\"" + this.sheet.TableFooterStyle + "\"\n";
            return _ret;
        }
        
        public string CreateColumns(ColumnItemCollection columns)
        {
            StringBuilder _ret = new StringBuilder();
            _ret.Append("\nWith Excel.ActiveSheet\n");
            int i = this.col;
            foreach (Column _col in columns)
            {
                string _columnCharacaterName = Util.GetColumnCharacterName(i);
                string _endCell = "\n.Range(\"" + _columnCharacaterName + "\" & " + var_count + "+" + row + ")";

                int _columnStartRow;
                if (this.sheet.CreateTableHeader)
                    _columnStartRow = row + 1;
                else
                    _columnStartRow = row;
                string _columnStart = _columnCharacaterName + _columnStartRow;

                string _range = "\n.Range(\"" + _columnStart + ":" + _columnCharacaterName + "\" & " + var_count + "+" + (row - 1) + ")";

                string _footerText = _col.FooterText;
                if (!string.IsNullOrEmpty(_footerText) || _col.SummaryFunction != SummaryType.No)
                {
                    if (!string.IsNullOrEmpty(_footerText))
                    {
                        _ret.Append(_endCell + ".Formula=\"" + _footerText + "\"");

                    }
                    else if (_col.SummaryFunction != SummaryType.No)
                    {
                        string _function = _col.SummaryFunction.ToString().ToUpper();
                        _ret.Append(_endCell + ".Formula=\"=" + _function + "(" + _columnStart + ":" + _columnCharacaterName + "\" & " + var_count + "+" + (row - 1) + " & \")\"");
                    }
                    _ret.Append(_endCell + ".EntireColumn.AutoFit\n");
                }

                if (!string.IsNullOrEmpty(_col.Format) || !string.IsNullOrEmpty(_col.Style))
                {
                    if (!string.IsNullOrEmpty(_col.Format))
                    {
                        string _format = _range + ".NumberFormatLocal=\"" + _col.Format + "\"";
                        _ret.Append(_format);
                    }
                    if (!string.IsNullOrEmpty(_col.Style))
                    {
                        string _style = _range + ".Style=\"" + _col.Style + "\"";
                        _ret.Append(_style);
                    }
                    _ret.Append(_range + ".EntireColumn.AutoFit\n");
                }
                i++;


            }
            _ret.Append("\nEnd With\n");
            return _ret.ToString();
        }

        private void PivotFields(ColumnItemCollection columns, StringBuilder pivotField)
        {
            foreach (Column _col in columns)
            {
                PivotField(_col, pivotField);
            }
            // return _pivotField.ToString();
        }

        private void PivotField(Column col, StringBuilder pivotField)
        {
            if (0 != col.Orientation)
                pivotField.Append("    pvtTable.PivotFields(\"" + col.HeaderText + "\").Orientation=" + col.Orientation + "\n");
            if (0 != col.Position)
                pivotField.Append("    pvtTable.PivotFields(\"" + col.HeaderText + "\").Position=" + col.Position + "\n");
            if (!col.SubTotals)
                pivotField.Append("    pvtTable.PivotFields(\"" + col.HeaderText + "\").Subtotals=Array(False, False, False, False, False, False, False, False, False, False, False, False)\n");
        }
        /// <summary>
        /// 创建数据透视表
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public string CreatePivotTable(ColumnItemCollection columns)
        {
            string _rangeStart;

            _rangeStart = Util.GetCellCharacterName(this.Row, this.Col);

            string _rangeEndColumn = Util.GetColumnCharacterName(this.Col + this.sheet.Columns.Count() - 1);
            string _rangeTemp = _rangeStart + ":" + _rangeEndColumn;

            StringBuilder _pivotTable = new StringBuilder();

            _pivotTable.Append("\nWith Excel.ActiveSheet");
            if (this.sheet.CreateTableHeader)
                _pivotTable.Append("\nSet pvtTable = 	Excel.ActiveWorkbook.PivotCaches.Create(1, \"" + _rangeTemp + "\" & " + this.VarCount + "+" + this.Row + "-1).CreatePivotTable(\"\")\n");
            else
                _pivotTable.Append("\nSet pvtTable = 	Excel.ActiveWorkbook.PivotCaches.Create(1, \"" + _rangeTemp + "\" & " + this.VarCount + "+" + this.Row + ").CreatePivotTable(\"\")\n");

            _pivotTable.Append("pvtTable.SmallGrid = False\n");

            this.PivotFields(columns, _pivotTable);

            _pivotTable.Append("With pvtTable\n");
            _pivotTable.Append(" If .DataFields.Count>1 then\n");
            _pivotTable.Append("    .DataPivotField.Orientation = 2\n");
            _pivotTable.Append("    .DataPivotField.Position = .ColumnFields.Count\n");
            _pivotTable.Append(" End If\n");
            _pivotTable.Append("End With\n");

            if (this.sheet.IsChart && this.sheet.TableType == TableType.PivotTable)
                _pivotTable.Append("Excel.ActiveWorkbook.Charts.add\n");

            _pivotTable.Append("End With\n");
            return _pivotTable.ToString();
        }
    }


}
